<?php
namespace  App\Http\Controllers\Radius;
use  App\Http\Controllers\Controller;

use App\Order;
use App\Rad_user;
use App\Rad_user_account;
use App\Usernew;
use Illuminate\Http\Request;

use App\Common\ErrorDefine;

use App\Radacct;
use App\Radcheck;
use App\Radgroupcheck;
use App\Radreply;
use App\Radusergroup;
use App\Rad_manage;
use App\Radgroupreply;
use App\Rad_users;
use App\Rad_account;
use App\Manage_user_list;
use DB;

use App\Line;
use App\Agent;


class  TestController extends Controller
{

    public function updateSpeed()
    {
        ini_set('max_execution_time', '0');
        $users = Rad_users::all();

        foreach($users as $item)
        {

            $reply =  Radreply::where('username',$item->username)->first();

            if(!empty($reply->value))
            {
                $item->speed = $reply->value;
                $item->save();
            }else{
                $item->speed = '';
                $item->save();
            }

        }

    }

    public function ManageUserList(Request $request)
    {
        $page_no = 0;
        $page_size = 10000000;
        $username = '';
        $end_date = date('Y-m-d H:i:s',time());
        $start_date = '1970-01-01 08:00:00';


        if($request->input('page_no'))
        {
            $page_no  = $request->input('page_no');
            if($page_no){
                $page_no = $page_no - 1;
                if(is_numeric($page_no)){
                    if($page_no<0){
                        $page_no = 0;
                    }
                }
            }
        }

        if($request->input('page_size'))
        {
            $page_size = $request->input('page_size');
            if($page_size){
                if($page_size <=0 ){
                    $page_size =  100;
                }
            }
        }

        if($request->has('username') && !empty($request->input('username')))
        {
            $username = $request->input('username');
        }


        // 查询时间
        if($request->has('end_date') && !empty($request->input('end_date')))
        {
            $end_date = $request->input('end_date');
        }

        if($request->has('start_date') && !empty($request->input('start_date')))
        {
            $start_date = $request->input('start_date');
        }





        $radcheck = new Radcheck();

        $result = $radcheck->leftJoin("users", function ($join) {
            $join->on("radcheck.id", "=", "users.user_id");
        })->leftJoin("radusergroup", function ($join) {
            $join->on("radusergroup.username", "=", "radcheck.username");
        })->leftJoin("radgroupcheck", function ($join) {
            $join->on("radgroupcheck.groupname", "=", "radusergroup.groupname");
        })->leftJoin("manage", function ($join) {
            $join->on("manage.manage_id", "=", "users.manage_id");
        })->leftJoin("radreply", function ($join) {
            $join->on("radreply.username", "=", "users.username");
        })->leftJoin("terrace", function ($join) {
            $join->on("users.terrace_id", "=", "terrace.id");
        })->whereIn('radcheck.id',Rad_users::where('user_id','>',0)->get(['user_id']))
            ->where('users.created_at','>',"{$start_date}")->where('users.created_at','<',"{$end_date}")
            ->where('users.username','like','%'.$username.'%')
            ->offset($page_no*$page_size)->limit($page_size)->get(array(
                'radcheck.id',
                'radcheck.username',
                'radcheck.value as password',
                'radgroupcheck.value as connect_num',
                'users.created_at',
                'users.account_type',
                'users.expired_at',
                'users.remark',
                'users.manage_id',
                'terrace.terrace_name',
                'manage.username as manage_name',
                'radreply.value',
            ))->toArray();
        foreach($result as $value)
        {
            $id = Manage_user_list::insertGetId($value);
            if($id)
            {
                echo 'manage数据插入成功------------------>'.$id.'<br>';
            }else{
                echo 'manage数据插入插入失败------------------>'.'<br>';
            }
        }

    }


    public function getIp(Request $request)
    {
        $reIP=$_SERVER["REMOTE_ADDR"];
        echo $reIP;
    }


    public function addAcouunt(Request $request)
    {
        if(empty($request->all()))
        {
            self::ShowError(ErrorDefine::EMPTY_POST_DATA);
            return;
        }

        //  名称
        if(!$request->has('username') && empty($request->input('username')) )
        {
            self::ShowError(ErrorDefine::NAME_NOT_EXIST);
            return;
        }
        $username = $request->input('username');

        //  密码
        if(!$request->has('password') && empty($request->input('password')))
        {
            self::ShowError(ErrorDefine::SHORT_OF_PASSWORD);
            return;
        }
        $password = $request->input('password');


        //  账号类型
        if(!$request->has('account_type') && empty($request->input('account_type')))
        {
            self::ShowError(ErrorDefine::ACCOUNT_TYPE_ONT_EXIST);
            return;
        }
        $account_type = $request->input('account_type');


        //  连接数
        if(!$request->has('concent_num') && empty($request->input('concent_num')) )
        {
            self::ShowError(ErrorDefine::CONCENT_NOT_EXIST);
            return;
        }
        $concent_num = $request->input('concent_num');

        //   检测连接数
        if($concent_num <=0 )
        {
            self::ShowError(ErrorDefine::ILLEGAL_CONNECT_NUM);
            return;
        }



        //   用户token
        if(!$request->has('token') && !empty($request->input('token')))
        {
            self::ShowError(ErrorDefine::TOKEN_NOT_EXIST);
            return;
        }
        $token = $request->input('token');

        //    备注
        $remark = '';
        if($request->input('remark'))
        {
            $remark = $request->input('remark');
        }

        //    agent.niumoip.com 平台标识
        $agent = 0;
        if($request->input('agent'))
        {
            $agent = $request->input('agent');
        }

        //    manage.niumoip.com 平台标识
        $manage = 0;
        if($request->input('manage'))
        {
            $manage = $request->input('manage');
        }

        //  订单号
        if(!$request->has('order_id') && empty($request->input('order_id')))
        {
            self::ShowError(ErrorDefine::ORDER_NO_NOT_EXIST);
            return;
        }
        $order_no = $request->input('order_id');


        if($account_type != '测试')
        {
            //   日期卡类型
            if(!$request->has('date_type') && empty($request->input('date_type')))
            {
                self::ShowError(ErrorDefine::DATE_TYPE_NOT_EXIST);
                return;
            }
            $date_type = $request->input('date_type');

            //     开通时间
            if(!$request->has('date') && empty($request->input('date')))
            {
                self::ShowError(ErrorDefine::DATE_NOT_EXIST);
                return;
            }
            $date = $request->input('date');

            if($date<=0) {
                self::ShowError(ErrorDefine::INVALID_DATE);
                return;
            }

            // 价格
            if(!$request->has('price') && empty($request->input('price')))
            {
                self::ShowError(ErrorDefine::PEICE_NOT_EXIST);
                return;
            }
            $price = $request->input('price');

        }




        //   用户类型   1   是普通用户  暂时默认普通
//        if(!$request->has('type') && empty($request->input('type')))
//        {
//            self::ShowError(ErrorDefine::USER_TYPE_NO_EXIST);
//            return;
//        }
//        $type = $request->input('type');





        //   获取用户ID
        $users = self::getTokenById($token);

        if(empty($users))
        {
            return self::ShowError(ErrorDefine::TOKEN_IS_EXPIRERD);
        }

        $manage_id =  $users[0]['manage_id'];


        //   判断   余额是否支持开户
        if($account_type !='测试')
        {
            if( $users[0]['balance'] < $price)
            {
                return self::ShowError(ErrorDefine::BALANCE_NOT_ENGOUTH);
            }
        }



        //   检测用户名是否存在
        $user = Radcheck::where('username',$username)->get(['id','username'])->toArray();
        if(!empty($user))
        {
            self::ShowError(ErrorDefine::USER_IS_EXIST);
            return;
        }

        // 开户时间  测试一小时
        if($account_type !='测试') {
            switch ($date_type) {
                case '日':
                    $expired_at = time() + 86400 * $date * 1;
                    break;
                case '周':
                    $expired_at = time() + 86400 * $date * 7;
                    break;
                case '月':
                    $expired_at = time() + 86400 * $date * 30;
                    break;
            }
        }else{
            $expired_at = time()+3600;
        }

        //   到期时间
        $time = date("d M Y H:i:s ",$expired_at);


//      添加用户
        $where = [];

        $where['username'] =$username;
        $where['value'] =$password;
        $where['attribute'] ='Cleartext-Password';
        $where['op'] =':=';

        $account_id = Radcheck::insertGetId($where);

        //   添加到期时间
        $arr = [];
        $arr['username'] =$username;
        $arr['attribute'] ='Expiration';
        $arr['op'] =':=';
        $arr['value'] = $time;

        Radcheck::insertGetId($arr);




        //   用户账号表
        $account = [];

        $account['manage_id'] = $manage_id;
        $account['user_id'] = $account_id;
        $account['username'] = $username;
        $account['password'] = $password;
        $account['terrace_id'] = 5;             //  平台标识  5 代理平台
        $account['con_max'] =$concent_num ;
        $account['created_at'] = date('Y-m-d H:i:s',time());
        $account['updated_at'] = date('Y-m-d H:i:s',time());
        $account['expired_at'] = date('Y-m-d H:i:s',$expired_at);
        $account['account_type'] = $account_type;
        $account['vip'] = 1;
        $account['remark'] = $remark;
        if($account_type !='测试')
        {
            $account['balance'] = $price;
        }

        $ret = Rad_users::insertGetId($account);



        //   用户所属组  即连接数
        $group = Radgroupcheck::where('value',$concent_num)->get()->toArray();

//        // 为空  则添加组
        if(empty($group))
        {
            //   添加组
            $group_parm = [];
            $group_parm['groupname'] = 'c'.$concent_num;
            $group_parm['attribute'] = 'Simultaneous-Use';
            $group_parm['op'] = ':=';
            $group_parm['value'] = $concent_num;
            Radgroupcheck::insertGetId($group_parm);
            //   添加组的属性

            //  属性1
            $group_attribute1= [];
            $group_attribute1['groupname'] = 'c'.$concent_num;
            $group_attribute1['attribute'] = 'Auth-Type';
            $group_attribute1['op'] = ':=';
            $group_attribute1['value'] = 'Local';


            //  属性2
            $group_attribute2= [];
            $group_attribute2['groupname'] = 'c'.$concent_num;
            $group_attribute2['attribute'] = 'Service-Type';
            $group_attribute2['op'] = ':=';
            $group_attribute2['value'] = 'Framed-User';


            //  属性3
            $group_attribute3= [];
            $group_attribute3['groupname'] = 'c'.$concent_num;
            $group_attribute3['attribute'] = 'Framed-IP-Address ';
            $group_attribute3['op'] = ':=';
            $group_attribute3['value'] = '255.255.255.255';

            $group_attribute4= [];
            $group_attribute4['groupname'] = 'c'.$concent_num;
            $group_attribute4['attribute'] = 'Framed-IP-Netmask';
            $group_attribute4['op'] = ':=';
            $group_attribute4['value'] = '255.255.255.0';

            Radgroupreply::insertGetId($group_attribute1);
            Radgroupreply::insertGetId($group_attribute2);
            Radgroupreply::insertGetId($group_attribute3);
            Radgroupreply::insertGetId($group_attribute4);
        }
//

        //   添加用户与组的对应关系
        $user_group = [];
        $user_group['username'] = $username;
        $user_group['groupname'] = 'c'.$concent_num;
        $user_group['priority'] = 1;
        Radusergroup::insertGetId($user_group);



        $radreply = Radreply::where('username',$username)->get()->toArray();

        if(empty($radreply))
        {
            $param['username'] = $username;
            $param['attribute'] = 'Mikrotik-Rate-Limit';
            $param['op'] = '=';
            $param['value'] = '2048k/2048k';
            $id = Radreply::insertGetId($param);
        }


        //   订单信息

        $oder = [];
        if($account_type !='测试')
        {
            $order['balance'] = $users[0]['balance']-$price;
            $order['date'] =$date;
            $order['price'] =$price;
        }else{
            $order['balance'] = $users[0]['balance'];
            $order['date'] = 0;
            $order['price'] =0;
        }

        $order['account_type'] = $account_type;
        $order['connect_num'] =$concent_num;
//        $order['agent_id'] =   $agent;                       //   代理平台标识
//        $order['manage_id'] =   $manage;                       //   代理平台标识


        $order['user_id'] = $manage_id;                 //   用户id

        $order['to_user'] =$account_id;
        $order['order_no'] =$order_no;
        $order['pay_channel'] =2;                    //    支付标识   余额  2


        $order['created_at'] = date('Y-m-d H:i:s',time());
        $order['updated_at'] = date('Y-m-d H:i:s',time());

        $id = Rad_order::insertGetId($order);

        if($id)
        {
            $orders = Rad_order::find($id);
            $orders->status = 1;
            $orders->save();
        }

        if($account_type !='测试')
        {
            $user = Rad_manage::find($manage_id);
            $user->balance = $user->balance - $price;
            $user->save();
        }

        return self::ShowMessage();

    }


    /**
     * 合并用户数据  第一步
     *
     */
    public function  mergeDataOne()
    {


        //   查询所有radius  已有用户
        $users = Radcheck::where('attribute','Cleartext-Password')->get()->toArray();

        foreach($users as $value)
        {

            //   检测  用户名是否存在  存在  不录入数据表

            $user = Rad_users::where('username',$value['username'])->get()->toArray();

            //   有数据跳出当前循环
            if(!empty($user))
            {
                continue;
            }

            $param = [];

            $param['username'] = $value['username'];
            $param['password'] = $value['value'];
            $param['user_id'] = $value['id'];
            $param['vip'] = 1;
            $param['terrace_id'] = 3;           //   manage.niumoip.com;

            //  到期时间
            $expirted = Radcheck::where('attribute','Expiration')->where('username',$value['username'])->get()->toArray();

            //   某些用户没有过期时间
            if(!empty($expirted))
            {
                $expirted_at =  date('Y-m-d H:i:s',strtotime($expirted[0]['value']));
                $param['expired_at'] = $expirted_at;
                $param['created_at'] = date('Y-m-d H:i:s',time());
                $param['updated_at'] =date('Y-m-d H:i:s',time());

            }else{
                $param['expired_at'] =  '2020-07-01 16:54:00';

            }

            //   查找用户所属分组  获取最大连接数

            $group = Radusergroup::where('username',$value['username'])->get()->toArray();

            if(!empty($group))
            {
                $con_max = Radgroupcheck::where('groupname',$group[0]['groupname'])->get()->toArray();
                $param['con_max'] =$con_max[0]['value'];

            }


            $id = Rad_users::insertGetId($param);

            echo '成功插入数据. --------'.$param['user_id'].'------------'.$param['username'].'------------'.$param['password'];
            echo '<br>';

        }


    }


    /**
     * 合并数据第二步
     *代理ID
     */
    public function mergeDataTwo()
    {
        $account = Rad_account::all()->toArray();

        foreach($account as $value )
        {

            $user = Rad_users::where('user_id',$value['account_id'])->get()->toArray();
            if(!empty($user))
            {
                $users =  Rad_users::find($value['account_id']);
                $users->manage_id = $value['user_id'];
                $users->save();
            }
        }

    }


    /**
     * 第三部
     *更新牛魔科技数到radius
     */
    public function mergeDataThree()
    {

        //   第一步   查询所有用户名不为汉字的数据

        $sql="length(name)=char_length(name)";
        $users = Usernew::whereRaw($sql)->get()->toArray();


        foreach($users as $value)
        {

//            dd($value);
            //   判断 radcheck  是否存在此用户

            $radcheck = Radcheck::where('username',$value['name'])->get()->toArray();

            if(empty($radcheck))    // 添加radcheck  数据
            {
                $where = [];
                $where['username'] =$value['name'];
                $where['value'] =$value['password'];
                $where['attribute'] ='Cleartext-Password';
                $where['op'] =':=';

                $account_id = Radcheck::insertGetId($where);
                if($account_id)
                {
                    echo 'Radcheck成功插入数据. --------用户ID'.$account_id.'------------'.$value['name'].'------------'.$value['password'];
                    echo '<br>';
                }

                //   添加到期时间
                $arr = [];
                $arr['username'] = $value['name'];
                $arr['attribute'] ='Expiration';
                $arr['op'] =':=';
                $arr['value'] = date("d M Y H:i:s ",strtotime($value['vip_expired']));

                $id = Radcheck::insertGetId($arr);
                if($id)
                {
                    echo 'Radcheck成功插入数据. --------'.$id.'------------'.$value['name'].'------------'.$arr['value'];
                    echo '<br>';
                }


                // 添加用户信息

                $_user = [];

                $_user['username'] = $value['name'];
                $_user['password'] = $value['password'];
                $_user['expired_at'] = $value['vip_expired'];
                $_user['con_max'] = $value['con_max'];
                $_user['mobile'] = $value['mobile'];
                $_user['email'] = $value['email'];
                $_user['qq'] = $value['qq'];
                $_user['vip'] = 1;
                $_user['terrace_id'] = 1;                           //   牛魔科技官网
                $_user['created_at'] = $value['created_at'];
                $_user['updated_at'] = $value['updated_at'];
                $_user['expired_at'] = $value['vip_expired'];
                $_user['recommend'] = $value['recommend'];
                $_user['remark'] = $value['remark'];
                $_user['manage_id'] = $value['agent_id'];
                $_user['user_id'] = $account_id;

                $id = Rad_users::insertGetId($_user);

                if($id)
                {
                    echo 'users成功插入数据. --------'.$id.'------------'.$value['name'].'------------'.$value['password'].'-----------'.$value['con_max'].'-----------'.$value['vip_expired'];
                    echo '<br>';
                }

            }


            //   检测  用户所属组  是否存在

            $groupreply = Radgroupreply::where('groupname',"c".$value['con_max'])->get()->toArray();

            if(empty($groupreply))
            {

                //   添加组的属性

                //  属性1
                $group_attribute1= [];
                $group_attribute1['groupname'] = 'c'.$value['con_max'];
                $group_attribute1['attribute'] = 'Auth-Type';
                $group_attribute1['op'] = ':=';
                $group_attribute1['value'] = 'Local';


                //  属性2
                $group_attribute2= [];
                $group_attribute2['groupname'] = 'c'.$value['con_max'];
                $group_attribute2['attribute'] = 'Service-Type';
                $group_attribute2['op'] = ':=';
                $group_attribute2['value'] = 'Framed-User';


                //  属性3
                $group_attribute3= [];
                $group_attribute3['groupname'] = 'c'.$value['con_max'];
                $group_attribute3['attribute'] = 'Framed-IP-Address ';
                $group_attribute3['op'] = ':=';
                $group_attribute3['value'] = '255.255.255.255';

                $group_attribute4= [];
                $group_attribute4['groupname'] = 'c'.$value['con_max'];
                $group_attribute4['attribute'] = 'Framed-IP-Netmask';
                $group_attribute4['op'] = ':=';
                $group_attribute4['value'] = '255.255.255.0';

                $one = Radgroupreply::insertGetId($group_attribute1);
                $two = Radgroupreply::insertGetId($group_attribute2);
                $three = Radgroupreply::insertGetId($group_attribute3);
                $four = Radgroupreply::insertGetId($group_attribute4);

                if($one && $two && $three && $four)
                {
                    echo '成功插入Radgroupreply数据 组数据. --------'.'c'.$value['con_max'];
                    echo '<br>';
                }

            }



            //    用户组信息的天加
            $groupcheck = Radgroupcheck::where('groupname',"c".$value['con_max'])->get()->toArray();

            if(empty($groupcheck))
            {
                //   添加组
                $group_parm = [];
                $group_parm['groupname'] = 'c'.$value['con_max'];
                $group_parm['attribute'] = 'Simultaneous-Use';
                $group_parm['op'] = ':=';
                $group_parm['value'] = $value['con_max'];
                $id = Radgroupcheck::insertGetId($group_parm);

                if($id)
                {
                    echo '成功插入Radgroupcheck数据 组数据. --------'.'c'.$value['con_max']."组信息添加成功";
                    echo '<br>';
                }
            }

            //   用户所属组信息的添加

            $group = Radusergroup::where('username',$value['name'])->get()->toArray();
            if(empty($group))
            {
                $user_group = [];
                $user_group['username'] = $value['name'];
                $user_group['groupname'] = 'c'.$value['con_max'];
                $user_group['priority'] = 1;
                $id = Radusergroup::insertGetId($user_group);

                if($id)
                {
                    echo '成功插入Radusergroup数据 组数据. --------'.'c'.$value['con_max']."组信息添加成功";
                    echo '<br>';
                }

            }

        }

    }




    /**
     * 第四部
     * 更新代理
     *
*/
    public function mergeDataFour()
    {
        $agents = Agent::all()->toArray();

       foreach($agents as $value)
       {
           $param = [];
           $param['username'] = $value['agent_account'];
           $param['password'] = $value['password'];
           $param['created_at'] = $value['created_at'];
           $param['updated_at'] = $value['updated_at'];
           $param['balance'] = $value['balance'];
           $param['day'] = 3;
           $param['week'] = 10;
           $param['month'] = 20;

           $id = Rad_manage::insertGetId($param);
           if($id)
           {
               echo '成功插入数据. --------'.$id.'------------用户名'.$value['agent_account'].'------------密码'.$param['password'].'------------余额'.$value['balance'];
               echo '<br>';
           }
       }

    }


    public function mergeDataFive()
    {

        $agents  = Agent::all()->toArray();

        foreach($agents as $value)
        {
            $manage = Rad_manage::where('username',$value['agent_account'])->where('password',$value['password'])->get()->toArray();

            $users = Rad_users::where('manage_id',$value['agent_id'])->get();
            foreach($users as $item)
            {
                $item->manage_id = $manage[0]['manage_id'];
                $item->save();
            }

        }


    }






}















